package com.querie.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.querie.config.Configuration;
import com.querie.model.beans.QueryLog;
import com.querie.sql.BaseDAO;
import com.querie.sql.ConnectionFactory;
import com.querie.sql.PreparedStatementSetter;
import com.querie.sql.ResultSetExtractor;

public  class QuerylogDAOImpl extends BaseDAO implements QuerylogDAO{
	public QueryLog load(final long queryid, final long sessionid) throws Exception{
		String sql = "SELECT * FROM QueryLog WHERE queryid=? and sessionid=?";
		return (QueryLog) getRow(sql, new PreparedStatementSetter() {
			public void prepare(PreparedStatement ps) throws SQLException {
				ps.setLong(1, queryid);
				ps.setLong(2, sessionid);
			}
		}, new ResultSetExtractor() {
			public Object extract(ResultSet rs) throws SQLException {
			QueryLog row  = new QueryLog();
				row.setSessionId(rs.getLong("sessionid"));
				row.setQuery(rs.getString("query"));
				row.setQueryId(rs.getLong("queryid"));
				return row;
			}
	});
	}

	public int delete(final long queryid, final long sessionid) throws Exception{
		String sql = "DELETE FROM QueryLog WHERE queryid=? and sessionid=?";
		return executeUpdate(sql, new PreparedStatementSetter() {
			public void prepare(PreparedStatement ps) throws SQLException {
				ps.setLong(1, queryid);
				ps.setLong(2, sessionid);
			}
		});
	}

	public int update(final QueryLog data) throws Exception{
		String sql = "UPDATE QueryLog SET query = ? WHERE queryid=? and sessionid=?";
		return executeUpdate(sql, new PreparedStatementSetter() {
			public void prepare(PreparedStatement ps) throws SQLException {
				ps.setString(1, data.getQuery());
				ps.setLong(2, data.getQueryId());
				ps.setLong(3, data.getSessionId());
			}
	});
	}

	public long insert(final QueryLog data) throws Exception{
		String sql = "INSERT INTO QueryLog ( query, queryid, sessionid) VALUES (?, ? )";
		long id = executeInsert(sql, new PreparedStatementSetter() {
			public void prepare(PreparedStatement ps) throws SQLException {
				ps.setString(1, data.getQuery());
				ps.setLong(2, data.getQueryId());
				ps.setLong(3, data.getSessionId());
			}
		});
		data.setSessionId(id);
		return id;
	}

	public List<QueryLog> queryAll()throws Exception{
		String sql = "SELECT * "+Configuration.getFilterQuery();
		return (List<QueryLog>) getList(sql, new ResultSetExtractor() {
			public Object extract(final ResultSet rs) throws SQLException {
				final QueryLog row = new QueryLog();
				row.setSessionId(rs.getLong("sessionid"));
				row.setQuery(rs.getString("query"));
				row.setQueryId(rs.getLong("queryid"));
				return row;
			}
	});
	}

	public List<QueryLog> queryAllWithPage(int startOfPage, int... pageSize)throws Exception{
		
		int page = 20;
		if (pageSize.length > 0)
		{
			page = pageSize[0];
		}
		
//		String sql = "SELECT * from querylog where sessionid in (select sessionid FROM querylog group by sessionid having count(sessionid) > 3) LIMIT "+startOfPage+" ,"+page ;
		String sql = "SELECT * "+Configuration.getFilterQuery()+" LIMIT "+startOfPage+" ,"+page ;
		return (List) getList(sql, new ResultSetExtractor() {
			public Object extract(ResultSet rs) throws SQLException {
				QueryLog row = new QueryLog();
				row.setSessionId(rs.getLong("sessionid"));
				row.setQuery(rs.getString("query"));
				row.setQueryId(rs.getLong("queryid"));
				return row;
			}
	});
	}

	public int countAll() throws Exception
	{
		   Connection c = null;
		    PreparedStatement ps = null;
		    ResultSet rs = null;
		    int count = 0;
		    try {
		      c = ConnectionFactory.getConnection();
		      ps = c.prepareStatement("SELECT COUNT(*) "+Configuration.getFilterQuery());
		      rs = ps.executeQuery();
		      while (rs.next()) {
		    	  count = rs.getInt(1);
		      }
		      
		    } catch (Exception e) {
		    	throw e;
		    }
		    return count;
	}
	 public ResultSetExtractor getListResultSetExtractor(){
		return new ResultSetExtractor() {
			public Object extract(ResultSet rs) throws SQLException {
				QueryLog row = new QueryLog();
				row.setSessionId(rs.getLong("sessionid"));
				row.setQuery(rs.getString("query"));
				row.setQueryId(rs.getLong("queryid"));
				return row;
			}
	};
	}

	}